
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;

namespace Common
{
	/// <summary>
	/// Description of ConditionSymptomDao.
	/// </summary>
	public class ConditionSymptomDao
	{
		private ConditionSymptomDao()
		{
		}
		
		public static int insert(ConditionSymptom cd)
		{
            string sql = "insert into ConditionSymptom(mstr_id,symptom_id,sequence)values(?,?,?);";
			OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = cd.MstrId;
            parameters[1] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[1].Value = cd.SymptomId;
            parameters[2] = new OleDbParameter("@sequence", OleDbType.Integer);
            parameters[2].Value = cd.Sequence;
			
			return AccessDBUtil.ExecuteInsert(sql,parameters);
		}

        public static int update(ConditionSymptom cd)
		{
            string sql = "update ConditionSymptom set symptom_id=?,sequence=? where id=?";
			OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[0].Value = cd.SymptomId;
            parameters[1] = new OleDbParameter("@sequence", OleDbType.Integer);
            parameters[1].Value = cd.Sequence;
			parameters[2] = new OleDbParameter("@id",OleDbType.Integer);
			parameters[2].Value=cd.Id;
			
			return AccessDBUtil.ExecuteNonQuery(sql,parameters);
			
			
		}
        public static ConditionSymptom getById(int id)
		{
            //to do
            string sql = "select cd.*,s.kind,s.state,s.part,s.complexity from ConditionSymptom cd inner join Symptom s on cd.symptom_id = s.id where cd.id=? order by cd.sequence";
			OleDbParameter[] parameters = new OleDbParameter[1];
			parameters[0] = new OleDbParameter("@id",OleDbType.Integer);
			parameters[0].Value=id;
			DataSet ds = AccessDBUtil.ExecuteQuery(sql,parameters);
            if (ds.Tables["ds"].Rows.Count == 0) return null;
			return Row2ConditionSymptom(ds.Tables["ds"].Rows[0]);
		}

        public static int getCountBySeq(int mstr_id,int seq)
        {
            
            string sql = "select * from ConditionSymptom where mstr_id = ? and sequence = ?";
            OleDbParameter[] parameters = new OleDbParameter[2];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            parameters[1] = new OleDbParameter("@sequence", OleDbType.Integer);
            parameters[1].Value = seq;

            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            return ds.Tables["ds"].Rows.Count;
     
        }
        public static int getCountBySymptomId(int mstr_id, int symptom_id)
        {

            string sql = "select * from ConditionSymptom where mstr_id = ? and symptom_id = ?";
            OleDbParameter[] parameters = new OleDbParameter[2];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            parameters[1] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[1].Value = symptom_id;

            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            return ds.Tables["ds"].Rows.Count;

        }




        public static int deleteById(int id)
        {
            string sql = "delete from ConditionSymptom where id=?";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@id", OleDbType.Integer);
            parameters[0].Value = id;
            return AccessDBUtil.ExecuteNonQuery(sql, parameters);
        }

        private static ConditionSymptom Row2ConditionSymptom(DataRow row)
		{
            ConditionSymptom cd = new ConditionSymptom();
            cd.Id = Int32.Parse(row["id"].ToString());
            cd.MstrId = Int32.Parse(row["mstr_id"].ToString());
            cd.SymptomId = Int32.Parse(row["symptom_id"].ToString());
            cd.Sequence = Int32.Parse(row["sequence"].ToString());
            cd.Kind = row["kind"].ToString();
            cd.Complexity = row["complexity"].ToString();
            cd.Part = row["part"].ToString();
            cd.State = row["state"].ToString();
            return cd;
		}

        public static List<ConditionSymptom> getAllConditionSymptoms(int mstr_id)
        {
            string sql = "select cd.*,s.kind,s.state,s.part,s.complexity from ConditionSymptom cd inner join Symptom s on cd.symptom_id = s.id where cd.mstr_id=? order by cd.sequence";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            List<ConditionSymptom> cds = new List<ConditionSymptom>();
            for (int i = 0; i < ds.Tables["ds"].Rows.Count; i++)
            {
                cds.Add(Row2ConditionSymptom(ds.Tables["ds"].Rows[i]));
            }
            return cds;
        }
		
	}
}
